/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Vector;
import javax.swing.table.DefaultTableModel;
import tuitionmanagement.entity.Fee;
import tuitionmanagement.util.DBUtil;

/**
 *
 * @author XUAN
 */
public class FeeLevelDao {

    private Connection con;
    private DBUtil db;
    private ResultSet rs;
    private PreparedStatement pst;
    private String lastError;
    private final String sql_selectFee = "select * from FeeLevel";
    private final String sql_insertFee = "insert into FeeLevel values(?,?,?,?) ";
    private final String sql_updateFee = "update FeeLevel set PaidTime = ?, MoneyTotal = ?, DateBegin =?, DateEnd =? WHERE FeeLevelId = ?";
    private final String sql_deleteFee = "delete from FeeLevel where FeeLevelId = ?";

    public FeeLevelDao() {
    }

    public ArrayList<Fee> getTableLevelDetail() {
        ArrayList<Fee> list = new ArrayList<Fee>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectFee);
            rs = pst.executeQuery();
            while (rs.next()) {
                Fee f = new Fee();
                f.setFeeLevelId(rs.getInt("FeeLevelId"));
                f.setPaidTime(rs.getInt("PaidTime"));
                f.setMoneyTotal(rs.getFloat("MoneyTotal"));
                f.setDateStart(rs.getDate("DateStart"));
                f.setDateEnd(rs.getDate("DateEnd"));

                list.add(f);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public DefaultTableModel GetTableStudent() {
        Vector col = new Vector();
        col.add("FeeLevelId");
        col.add("PaidTime");
        col.add("MoneyTotal");
        col.add("DateStart");
        col.add("DateEnd");
        Vector datarows = new Vector();
        ResultSet rs = null;
        try {
            Connection conn = DBUtil.getConn();
            PreparedStatement pst = con.prepareStatement(sql_selectFee);
            rs = pst.executeQuery();
            System.out.println("abc");
            while (rs.next()) {
                Vector v = new Vector();
                String a = rs.getString(1);
                v.add(a);
                String b = rs.getString(2);
                v.add(b);
                String c = rs.getString(3);
                v.add(c);
                String d = rs.getString(4);
                v.add(d);
                String e = rs.getString(5);
                v.add(e);
                datarows.add(v);
            }
            conn.close();
        } catch (Exception ex) {
            System.out.println("asd" + ex.getMessage());
        }
        return new DefaultTableModel(datarows, col);
    }

    public boolean addfee(Fee f) {
        if (!f.validate()) {
            setLastError(f.getLastError());
            return false;
        }
        try {
//<<<<<<< .mine
            con = DBUtil.getConn();
            pst = con.prepareStatement(sql_insertFee);           
            pst.setInt(1, f.getPaidTime());
            pst.setFloat(2, f.getMoneyTotal());
            pst.setDate(3, f.getDateStart());
            pst.setDate(4, f.getDateEnd());
//=======
            con = DBUtil.getConn();
            pst = con.prepareStatement(sql_insertFee);
//>>>>>>> .r45

            pst.setInt(1, f.getPaidTime());
            pst.setFloat(2, f.getMoneyTotal());
            pst.setDate(3, f.getDateStart());
            pst.setDate(4, f.getDateEnd());

            if (pst.executeUpdate() > 0) {
                setLastError("Add FeeLevel's information successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public ArrayList<Fee> readByAll() {
        ArrayList<Fee> list = new ArrayList<Fee>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectFee);
            rs = pst.executeQuery();
            while (rs.next()) {
                Fee f = new Fee();
                f.setFeeLevelId(rs.getInt("FeeLevelId"));
                f.setPaidTime(rs.getInt("PaidTime"));
//<<<<<<< .mine
                f.setMoneyTotal(rs.getInt("MoneyTotal"));
                f.setDateStart(rs.getDate("DateBegin"));
//=======
                f.setMoneyTotal(rs.getFloat("MoneyTotal"));
                f.setDateStart(rs.getDate("DateBegin"));
//>>>>>>> .r45
                f.setDateEnd(rs.getDate("DateEnd"));

                list.add(f);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public boolean update(Fee f) {
//<<<<<<< .mine
        con = DBUtil.getConn();

//=======
        con = DBUtil.getConn();
//>>>>>>> .r45
        try {
            pst = con.prepareStatement(sql_updateFee, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//<<<<<<< .mine
            pst.setInt(1, f.getFeeLevelId());
            pst.setFloat(2, f.getPaidTime());
            pst.setFloat(3, f.getMoneyTotal());
            pst.setDate(4, new java.sql.Date(f.getDateStart().getTime()));
            pst.setDate(5, new java.sql.Date(f.getDateStart().getTime()));
            pst.executeUpdate();
            //rs.first();
            //rs.updateDate(1,(Date) rate.getDateRate());
            //rs.updateFloat(2,rate.getUsd());
            //rs.updateFloat(3,rate.getVnd());
            //rs.updateRow();
//=======

         

//            rs.updateInt(1, f.getPaidTime());
//            rs.updateFloat(2, f.getMoneyTotal());
//            rs.updateDate(3, f.getDateStart());
//            rs.updateDate(4, f.getDateEnd());
            pst.setInt(1,f.getPaidTime());
            pst.setFloat(2,f.getMoneyTotal());
            pst.setDate(3,new java.sql.Date(f.getDateStart().getTime()));
            pst.setDate(4,new java.sql.Date(f.getDateEnd().getTime()));
            
            pst.setInt(5, f.getFeeLevelId());

            int effectedRows =pst.executeUpdate();

//>>>>>>> .r45
            con.close();
            setLastError("Update FeeLevel successfully!");
            if(effectedRows>0)  return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public boolean delete(int FeeLevelId) {
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_deleteFee, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, FeeLevelId);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete FeeLevel successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
//<<<<<<< .mine
            DBUtil.closeConn(con);
            return false;
//=======
//            DBUtil.closeConn(con);
//>>>>>>> .r45
        }
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
